package omg.ligong.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import omg.ligong.dao.CollegeDAO;
import omg.ligong.db.ConnectionManager;
import omg.ligong.domain.College;

public class CollegeDAOImpl implements CollegeDAO {
	public final static String TABLE_NAME = "college";
	public final static String FIELD_ID = "college_guid";
	public final static String FIELD_NAME = "college_name";
	public final static String FIELD_DESC = "college_summary";
	public final static String FIELD_DATE = "create_date";
	public final static String FIELD_ENABLE = "college_enable";

	public void addCollege(College college) throws Exception {
		String sql = new StringBuffer("INSERT INTO ").append(TABLE_NAME)
				.append("(").append(FIELD_NAME).append(",").append(FIELD_DESC)
				.append(",").append(FIELD_DATE).append(",")
				.append(FIELD_ENABLE).append(") VALUES (?,?,?,?)").toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			try {
				pstmt.setString(1, college.getName());
				pstmt.setString(2, college.getDescription());
				pstmt.setDate(3, college.getCreateDate());
				pstmt.setString(4, college.isEnable() ? "1" : "0");
				pstmt.executeUpdate();
			} finally {
				pstmt.close();
			}
		} finally {
			conn.close();
		}

	}

	public int countCollege() throws Exception {
		String sql = new StringBuffer("SELECT COUNT(*) FROM ").append(
				TABLE_NAME).toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			Statement stmt = conn.createStatement();
			try {
				ResultSet rs = stmt.executeQuery(sql);
				try {
					if (rs.next()) {
						return rs.getInt(1);
					}
				} finally {
					rs.close();
				}
			} finally {
				stmt.close();
			}
		} finally {
			conn.close();
		}
		return 0;
	}

	public void delCollege(String className) throws Exception {
		String sql = new StringBuffer("DELETE FROM ").append(TABLE_NAME)
				.append(" WHERE ").append(FIELD_NAME).append("=?").toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			try {
				pstmt.setString(1, className);
				pstmt.executeUpdate();
			} finally {
				pstmt.close();
			}
		} finally {
			conn.close();
		}
	}

	public College findCollegeByName(String className) throws Exception {
		String sql = new StringBuffer("SELECT * FROM ").append(TABLE_NAME)
				.append(" WHERE ").append(FIELD_NAME).append("=?").toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			try {
				pstmt.setString(1, className);
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) {
					return new College(rs.getInt(FIELD_ID), rs
							.getString(FIELD_NAME), rs.getString(FIELD_DESC),
							"1".equals(rs.getString(FIELD_ENABLE)) ? true
									: false, rs.getDate(FIELD_DATE));
				}
			} finally {
				pstmt.close();
			}
		} finally {
			conn.close();
		}
		return null;
	}

	public List listCollege(int offset, int count) throws Exception {
		List list = new ArrayList();
		String sql = new StringBuffer("SELECT * FROM ").append(TABLE_NAME)
				.append(" limit ").append(offset).append(",").append(count)
				.toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			Statement stmt = conn.createStatement();
			try {
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					list.add(new College(rs.getInt(FIELD_ID), rs
							.getString(FIELD_NAME), rs.getString(FIELD_DESC),
							"1".equals(rs.getString(FIELD_ENABLE)) ? true
									: false, rs.getDate(FIELD_DATE)));
				}
			} finally {
				stmt.close();
			}
		} finally {
			conn.close();
		}
		return list;
	}

	public void modifyCollege(String srcName, String destName, String desc,boolean enable)
			throws Exception {
		String sql = new StringBuffer("UPDATE ").append(TABLE_NAME).append(
				" SET ").append(FIELD_NAME).append("=?,").append(FIELD_DESC).append("=?,").append(FIELD_ENABLE)
				.append("=? WHERE ").append(FIELD_NAME).append("=?").toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			try {
				pstmt.setString(1, destName);
				pstmt.setString(2, desc);
				pstmt.setString(3, enable ? "1" : "0");
				pstmt.setString(4, srcName);
				pstmt.executeUpdate();
			} finally {
				pstmt.close();
			}
		} finally {
			conn.close();
		}
	}

	public void setUsable(String name, boolean enable) throws Exception {
		String sql = new StringBuffer("UPDATE ").append(TABLE_NAME).append(
				" SET ").append(FIELD_ENABLE).append("=? WHERE ").append(
				FIELD_NAME).append("=?").toString();
		Connection conn = ConnectionManager.getInstance().getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			try {
				pstmt.setString(1, enable ? "1" : "0");
				pstmt.setString(2, name);
				pstmt.executeUpdate();
			} finally {
				pstmt.close();
			}
		} finally {
			conn.close();
		}
	}

}
